package com.example.check.mapper;

import com.example.check.pojo.ItemOverview;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 物品总览Mapper接口
 */
@Mapper
public interface ItemOverviewMapper {
    
    /**
     * 查询所有物品总览（包括公共物品和指定用户的自定义物品）
     */
    @Select("SELECT * FROM item_overview WHERE is_active = 1 AND (user_id IS NULL OR user_id = #{userId}) ORDER BY created_at DESC")
    List<ItemOverview> getAll(@Param("userId") Integer userId);
    
    /**
     * 查询所有公共物品（user_id为NULL）
     */
    @Select("SELECT * FROM item_overview WHERE is_active = 1 AND user_id IS NULL ORDER BY created_at DESC")
    List<ItemOverview> getPublicItems();
    
    /**
     * 根据ID查询物品总览
     */
    @Select("SELECT * FROM item_overview WHERE id = #{id}")
    ItemOverview getById(@Param("id") Integer id);
    
    /**
     * 根据分类ID查询物品总览
     */
    @Select("SELECT * FROM item_overview WHERE category_id = #{categoryId} AND is_active = 1 ORDER BY created_at DESC")
    List<ItemOverview> getByCategoryId(@Param("categoryId") Integer categoryId);
    
    /**
     * 根据名称模糊查询物品总览
     */
    @Select("SELECT * FROM item_overview WHERE name LIKE CONCAT('%', #{name}, '%') AND is_active = 1 ORDER BY created_at DESC")
    List<ItemOverview> getByName(@Param("name") String name);
    
    /**
     * 根据名称和分类ID精确查询物品总览
     */
    @Select("SELECT * FROM item_overview WHERE name = #{name} AND category_id = #{categoryId} AND is_active = 1 LIMIT 1")
    ItemOverview getByNameAndCategoryId(@Param("name") String name, @Param("categoryId") Integer categoryId);
    
    /**
     * 添加物品总览
     */
    @Insert("INSERT INTO item_overview (name, category_id, description, image_url, tags, is_active, user_id, created_at, updated_at) " +
            "VALUES (#{name}, #{categoryId}, #{description}, #{imageUrl}, #{tags, typeHandler=com.example.check.config.JsonTypeHandler}, #{isActive}, #{userId}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int add(ItemOverview itemOverview);
    
    /**
     * 更新物品总览
     */
    @Update("UPDATE item_overview SET name = #{name}, category_id = #{categoryId}, description = #{description}, " +
            "image_url = #{imageUrl}, tags = #{tags, typeHandler=com.example.check.config.JsonTypeHandler}, " +
            "is_active = #{isActive}, updated_at = NOW() WHERE id = #{id}")
    int update(ItemOverview itemOverview);
    
    /**
     * 删除物品总览（软删除）
     */
    @Update("UPDATE item_overview SET is_active = 0, updated_at = NOW() WHERE id = #{id}")
    int deleteById(@Param("id") Integer id);
    
    /**
     * 统计物品总览数量
     */
    @Select("SELECT COUNT(*) FROM item_overview WHERE is_active = 1")
    int count();
    
    /**
     * 根据分类统计物品总览数量
     */
    @Select("SELECT COUNT(*) FROM item_overview WHERE category_id = #{categoryId} AND is_active = 1")
    int countByCategoryId(@Param("categoryId") Integer categoryId);
    
    /**
     * 根据条件筛选物品总览（支持分类、关键词筛选，包括公共物品和指定用户的自定义物品）
     */
    @Select("<script>" +
            "SELECT io.* FROM item_overview io " +
            "LEFT JOIN item_categories ic ON io.category_id = ic.id " +
            "WHERE io.is_active = 1 " +
            "AND (io.user_id IS NULL OR io.user_id = #{userId}) " +
            "<if test='categoryCode != null and categoryCode != \"\"'>" +
            "AND ic.code = #{categoryCode} " +
            "</if>" +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (io.name LIKE CONCAT('%', #{keyword}, '%') OR io.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "ORDER BY io.created_at DESC" +
            "</script>")
    List<ItemOverview> getFilteredItems(@Param("userId") Integer userId, @Param("categoryCode") String categoryCode, @Param("keyword") String keyword);
}


